package com.apexedu.portal.controller;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.apexedu.framework.dao.BaseDao;
import com.apexedu.framework.util.DBUtil;
import com.apexedu.framework.util.Pager;
import com.apexedu.identity.IdConstants;
import com.apexedu.identity.entity.TSysMenu;
import com.apexedu.identity.entity.TSysUser;
import com.apexedu.portal.constant.ActionConstant;
import com.apexedu.portal.constant.Constants;
import com.apexedu.portal.entity.Lbnewsattachment;
import com.apexedu.portal.entity.Lbxx;
import com.apexedu.portal.entity.Schedule;
import com.apexedu.portal.entity.TDb;
import com.apexedu.portal.entity.TLbxx;
import com.apexedu.portal.entity.TLbxxMx;
import com.apexedu.portal.entity.TMessage;
import com.apexedu.portal.entity.TUserReport;
import com.apexedu.portal.entity.TWz;
import com.apexedu.portal.entity.Tblog;
import com.apexedu.portal.service.impl.MhShmyServiceImpl;
import com.apexedu.portal.util.DBMUtil;
import com.apexedu.portal.util.LittleTimer;
import com.apexedu.sys.entity.TSysReportfactory;
import com.apexedu.util.StringUtil;

/**
 * 门户4.0展示界面 上海美术学院
 * Created by ly on 2015/3/5.
 */
@Controller
public class MhShgymyController extends BaseController {

	@Autowired
	BaseDao baseDao;
	@Resource
	private MhShmyServiceImpl service;

	private static final String[] weeks = new String[] { "周日", "周一", "周二", "周三", "周四", "周五", "周六" };
	private StringBuffer lm = new StringBuffer();
	private StringBuffer lmbh = new StringBuffer();
	private Integer group_num = 0;
	private Integer analysis_num = 0;
	HashMap<String, String> id_name_list = new HashMap<String, String>();
	HashMap<String, String[]> id_value_list = new HashMap<String, String[]>();
	HashMap<String, String[]> key_value_list = new HashMap<String, String[]>();

	/**
	 * 初始界面-V1
	 *
	 * @return 初始界面
	 */
	@Deprecated
	@RequestMapping(value = ActionConstant.MhShmy.RequestMapping_init_v1, method = RequestMethod.GET)
	public String init(HttpServletRequest request) {
		debug("<<{}>> init start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		try {
			String type = getUsertype(request); //获取用户类型
			initTzgg(request, type); //初始化通知公告
			initDyfw(request, type); //初始化订阅服务
			initLczx(request); //初始化流程中心
			initRc(request); //初始化日程
			return ActionConstant.MhShmy.Forward_init;
		} catch (Exception e) {
			error("<<{}>> init error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		} finally {
			debug("<<{}>> init end : {}", this.getClass().getSimpleName(), timer.end());
		}
	}

	/**
	 * 初始界面-V2
	 *
	 * @return 初始界面
	 */
	@RequestMapping(value = ActionConstant.MhShmy.RequestMapping_init, method = RequestMethod.GET)
	public String initV2(HttpServletRequest request) {
		debug("<<{}>> init start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		//登录用户
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);

		try {
			//获取用户类型
			String type = getUsertype(request);

			//初始化订阅服务
			initDyfw(request, type);
			System.out.println("订阅服务完成");

			//初始化代办流程（从LiveBOS的流程--->门户的流程表）
			flushWorkflowFromLiveBOS2Portal(user.getUserloginid(), ActionConstant.Process_Charge);
			System.out.println("流程1");
			//初始化待办消息（从门户的流程表--->门户的消息表）
			flushMessageFromPortalWorkflow2PortalMessage(user.getUserloginid(), ActionConstant.Process_Charge);
			System.out.println("流程2");
			
			/* 查询(当前用户)的消息列表 */
			String sqlSelectUnDo = " select * from t_message where clzt=1 and userid=?";
			List<TMessage> tMessageList = DBUtil.queryAllBeanList(sqlSelectUnDo, TMessage.class, user.getUserloginid());
			if (null != tMessageList) {
				request.setAttribute("todomessagenum", tMessageList.size() + "");
			} else {
				request.setAttribute("todomessagenum", "0");
			}
			request.setAttribute("todomessage", tMessageList);
			System.out.println("待办完成");
			
			//初始化其他信息-消息信息，在门户后台门户组件-列表信息管理中配置的
			initMessage(request, user, "3");
			System.out.println("消息完成");

			/* 获取日期，教学周周次等 */
			Calendar cal = Calendar.getInstance();
			String week = weeks[cal.get(Calendar.DAY_OF_WEEK) - 1];
			request.setAttribute("weekday", week); //周几
			request.setAttribute("year", cal.get(Calendar.YEAR));
			request.setAttribute("month", cal.get(Calendar.MONTH) + 1);
			request.setAttribute("date", cal.get(Calendar.DAY_OF_MONTH));
			Object zc = new DBMUtil().queryFieldValue("select zbh from t_zc where to_char(sysdate,'yyyyMMdd') between to_char(ksrq,'yyyyMMdd') and to_char(jsrq,'yyyyMMdd')");
			request.setAttribute("zc", String.valueOf(zc));
			System.out.println("日历完成");
			
			/* 获取未读邮件数 */
			Integer unReadMailNumber = 0;
			unReadMailNumber = service.getUnReadMailNumber(user.getUserloginid());
			request.setAttribute("unReadMailNumber", unReadMailNumber);
			System.out.println("邮件完成");
		} catch (Exception e) {
			error(e, "<<{}>> init error : {}", this.getClass().getSimpleName(), e);
			//throw new RuntimeException(e);
		} finally {
			debug("<<{}>> init end : {}", this.getClass().getSimpleName(), timer.end());
		}
		return ActionConstant.MhShmy.Forward_init_new;
	}

	/**
	 * 初始化服务资源
	 * 包含公共服务和自己订阅的服务
	 *
	 * @param request --
	 * @param type 用户类型 参考{@link Constants.MhShghUserType}
	 */
	private void initDyfw(HttpServletRequest request, String type) {
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		//request.setAttribute("userid", user.getUserloginid());
		request.setAttribute("user", user);

		String userId = user.getUserloginid();

		String mrfwid = StringUtil.EMPTY; //默认服务id
		String dyfwid = StringUtil.EMPTY; //订阅服务id
		if (Constants.MhUserType.LS.equals(type)) { //教职工
			mrfwid = "server_WAR_nj612portlet_INSTANCE_aaiNF15s1md6";
			dyfwid = "server_WAR_nj612portlet_INSTANCE_7iINqGjAAV73";
		} else if (Constants.MhUserType.LD.equals(type)) { //校领导
			//mrfwid = "server_WAR_nj612portlet_INSTANCE_iHP4bwLDiNYC";
			//dyfwid = "server_WAR_nj612portlet_INSTANCE_BkG18oMpTY3V";
			//校领导和教职工使用同一个服务配置
			mrfwid = "server_WAR_nj612portlet_INSTANCE_aaiNF15s1md6";
			dyfwid = "server_WAR_nj612portlet_INSTANCE_7iINqGjAAV73";
		} else if (Constants.MhUserType.XS.equals(type)) { //学生
			mrfwid = "server_WAR_nj612portlet_INSTANCE_b0pMQmbjftws";
			dyfwid = "server_WAR_nj612portlet_INSTANCE_8IoK6fuYENNz";
		}
		request.setAttribute("mrfwid", mrfwid);
		request.setAttribute("dyfwid", dyfwid);

		List<TSysMenu> mrfw = getDyfwList(userId, mrfwid);
		request.setAttribute("mrfw", mrfw);

		List<TSysMenu> dyfw = getDyfwList(userId, dyfwid);
		request.setAttribute("dyfw", dyfw);
	}

	/**
	 * 【方法名】：刷新流程信息（LiveBOS系统--->门户流程表）<br/>
	 * 【简介】：<br/>
	 * 1、将（指定用户、指定处理状态）的流程从各个业务系统中查询出来<br/>
	 * 2、将查询出的数据汇总<br/>
	 * 3、将门户的类别消息明细表中（指定用户、指定处理状态）的旧数据删除，并插入刚刚查出的汇总数据<br/>
	 * 【创建时间】：2016年9月7日 上午10:57:50<br/>
	 * 
	 * @param userId 待刷新流程的用户
	 * @param workflowTypeId 待刷新流程的状态值
	 */
	private void flushWorkflowFromLiveBOS2Portal(String userId, String workflowTypeId) {
		try {
			/*
			 * 查询类别信息明细汇总列表
			 */
			//类别信息明细汇总列表
			List<TLbxxMx> lbxxmxList = new ArrayList<TLbxxMx>();
			//查询类别信息列表
			List<TLbxx> lbxxList = DBUtil.queryAllBeanList("select * from t_lbxx where state=1 and upid='" + workflowTypeId + "' order by orderno", TLbxx.class);
			//迭代类别信息列表，从各个业务系统中查询出类别信息明细列表，并加入汇总列表中
			if (!lbxxList.isEmpty()) {
				for (TLbxx lbxx : lbxxList) {
					//数据库名称
					String dsNameTemp = lbxx.getDb();
					//执行的sql
					String sqlTemp = lbxx.getSql().replaceAll("\\{userid\\}", userId).replaceAll("\\{db\\}", dsNameTemp);
					//查询出当前类别下的信息明细列表
					List<TLbxxMx> lbxxmxListTemp = new DBMUtil(dsNameTemp).queryAllBeanList(sqlTemp, TLbxxMx.class);
					//将当前信息明细列表加入汇总列表中
					lbxxmxList.addAll(lbxxmxListTemp);
				}
			}

			/*
			 * 1、清除旧的无效数据
			 * 2、如果状态为“待办流程”的类表信息明细汇总列表不为空，则将其加入门户系统的相应表中
			 */
			/* 删除列表明细表中（指定用户、指定处理状态）的旧数据 */
			String delsql = "delete T_Lbxx_Mx where processtype=? and userid=?";
			DBUtil.executeSQL(delsql, workflowTypeId, userId);
			/* 插入新数据 */
			if (null != lbxxmxList && lbxxmxList.size() > 0 && workflowTypeId.equals(ActionConstant.Process_Charge)) {
				/* 将汇总信息明细列表中的数据插入信息明细表中 */
				String insertsql = "insert into T_Lbxx_Mx(wid,id,url,title,time,state,processtype,userid,db) select sys_guid(),?,?,?,?,?,?,?,? from dual";
				/* 循环执行数据查询，当列表很大时会非常耗时，需要改为批处理 */
				List<Object[]> paramsList = new ArrayList<Object[]>();
				Object[] params = null;
				for (int i = 0; i < lbxxmxList.size(); i++) {
					TLbxxMx tlbxxmx = lbxxmxList.get(i);
					//DBUtil.executeSQL(insertsql, tlbxxmx.getId(), tlbxxmx.getUrl(), tlbxxmx.getTitle(), tlbxxmx.getTime(), tlbxxmx.getState(), workflowTypeId, userId, tlbxxmx.getDb());

					params = new Object[8];
					params[0] = tlbxxmx.getId();
					params[1] = tlbxxmx.getUrl();
					params[2] = tlbxxmx.getTitle();
					params[3] = tlbxxmx.getTime();
					params[4] = tlbxxmx.getState();
					params[5] = workflowTypeId;
					params[6] = userId;
					params[7] = tlbxxmx.getDb();
					paramsList.add(params);
				}
				DBUtil.executeBatch(insertsql, paramsList);
			}
		} catch (Exception e) {
			error("<<{}>> initLcxx error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		}
	}

	/**
	 * 【方法名】：刷新门户流程类型的消息（门户流程表--->门户消息表）<br/>
	 * 【简介】：只处理了待办状态的流程<br/>
	 * 1、将消息表中（指定用户）的（状态为处理中：1）的数据处理状态更新为已中止：3<br/>
	 * 2、将类别信息明细表中(当前用户)（状态为待办流程）的记录插入消息表（忽略已经存在的）<br/>
	 * 3、对比消息表和类别信息明细表中(当前用户)的数据，将存在于类别信息明细表且状态为待办流程：3的类别消息明细对应的消息的处理状态设置为处理中：1<br/>
	 * 【创建时间】：2016年9月7日 上午11:04:41<br/>
	 * 
	 * @param userId 待刷新流程的用户
	 * @param workflowTypeId 待刷新流程的状态值
	 */
	private void flushMessageFromPortalWorkflow2PortalMessage(String userId, String workflowTypeId) {
		//将消息表中(当前用户)（处理状态为处理中：1）消息的处理状态设置为已中止：3
		String sqlUpdate = "update t_message tm set tm.clzt=3 where userid=? and tm.clzt=1";
		DBUtil.executeSQL(sqlUpdate, userId);

		//将类别信息明细表中(当前用户)（状态为待办流程）的记录插入消息表（忽略已经存在的）
		String sqlInsert = "  insert into t_message(wid,bt,nr,url,zt,userid,ly,type,broadcastusers,sj) " + "select sys_guid(),t.title,t.title,t.url,'unread',t.userid,t.db,'user','',sysdate  from t_lbxx_mx t " + "where 1=1 "
				+ " and t.url not in(select url from t_message m  where m.userid =?) "
				//+ " and not exists (select 1 from t_message m where m.url=t.url) "
				+ " and t.userid=? and t.processtype=?";
		DBUtil.executeSQL(sqlInsert, userId, userId, workflowTypeId);

		//对比消息表和类别信息明细表中(当前用户)的数据，将存在于类别信息明细表且状态为待办流程：3的类别消息明细对应的消息的处理状态设置为处理中：1
		String sqlUpdate2 = "update t_message tm set tm.clzt=1 where 1=1 and tm.url in (select url from t_lbxx_mx tmx where tmx.userid=? and tmx.processtype=?) and tm.userid=?";
		DBUtil.executeSQL(sqlUpdate2, userId, workflowTypeId, userId);
	}

	/**
	 * 初始化其他消息
	 *
	 * @param request
	 */
	private void initMessage(HttpServletRequest request, TSysUser user, String upid) {
		TDb tdb = new TDb();
		List<TLbxx> lbxxList = DBUtil.queryAllBeanList("select * from t_lbxx where state=1 and upid='" + upid + "' order by orderno", TLbxx.class);
		List<TLbxxMx> lbxxes = new ArrayList<TLbxxMx>();
		if (!lbxxList.isEmpty()) {
			for (TLbxx lbxx : lbxxList) {
				try {
					DBMUtil dbm = new DBMUtil(lbxx.getDb());
					String sql = lbxx.getSql().replaceAll("\\{userid\\}", user.getUserloginid());
					lbxxes.addAll(dbm.queryAllBeanList(sql, TLbxxMx.class));
				} catch (Exception e) {
					error("<<{}>> initLcxx error : {}", this.getClass().getSimpleName(), e);
					throw new RuntimeException(e);
				}
			}
		}
		request.setAttribute("message", lbxxes);
	}

	/**
	 * 初始化通知公告
	 *
	 * @param request
	 */
	private void initTzgg(HttpServletRequest request, String type) throws Exception {
		request.setAttribute("fwzx", getTzgg(new DBMUtil(), null, "select t.wid as id,t.pproblem as title,t.ptime,t.sid as id from t_PROBLEM t order by t.ptime desc"));
	}

	/**
	 * 初始化日程信息
	 *
	 * @param request --
	 */
	public void initRc(HttpServletRequest request) {
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		try {
			String sql = "select t.begintime,t.location,t.title from TSCHEDULE t left join tuser a on t.creator=a.id where a.userid=?" + " and (to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd')"
					+ " between to_date(to_char(t.begindate,'yyyyMMdd'),'yyyyMMdd') and to_date(to_char(t.enddate,'yyyyMMdd'),'yyyyMMdd') )";
			List<Schedule> list = new DBMUtil("oa").queryAllBeanList(sql, Schedule.class, user.getUserloginid());
			request.setAttribute("rcap", list);
		} catch (Exception e) {
			error("<<{}>> initLczx error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		}
	}

	/**
	 * 初始化流程中心
	 *
	 * @param request --
	 */
	private void initLczx(HttpServletRequest request) {
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		List<TLbxx> lbxxList = DBUtil.queryAllBeanList("select * from t_lbxx where state=1 and upid=5 order by orderno", TLbxx.class);
		List<Lbxx> lbxxes = new ArrayList<Lbxx>();
		if (!lbxxList.isEmpty()) {
			for (TLbxx lbxx : lbxxList) {
				try {
					lbxxes.addAll(new DBMUtil(lbxx.getDb()).queryAllBeanList(lbxx.getSql().replaceAll("\\{userid\\}", user.getUserloginid()), Lbxx.class));
				} catch (Exception e) {
					error("<<{}>> initLczx error : {}", this.getClass().getSimpleName(), e);
					throw new RuntimeException(e);
				}
			}
		}
		request.setAttribute("lczx", lbxxes);
	}

	/**
	 * 周会议
	 *
	 * @return 周会议html代码
	 */
	@RequestMapping(value = ActionConstant.MhShmy.RequestMapping_getZhy, method = RequestMethod.GET)
	@ResponseBody
	public String getZhy() {
		LittleTimer timer = new LittleTimer();
		try {
			DBMUtil dbutil = new DBMUtil("oa");
			/**
			 * 查询OA数据库
			 * t_zc 表
			 * 
			 * @参数 mc 名称
			 */
			String str = (String) dbutil.queryFieldValue("select zc.mc from t_zc zc where to_date(to_char(sysdate,'yyyyMMdd'),'yyyy-MM-dd') between ksrq and jsrq");
			String mc = str != null ? str : null;
			if (mc == null) {
				//                throw new RuntimeException("一周会议无法获取周次名称!");
			}
			debug("<<{}>> doView : zc : {}", this.getClass().getSimpleName(), mc);

			String sql = "select (select decode( to_char(min(gg.kssj),'D'),1,7,to_char(min(gg.kssj),'D')-1) from dual)week ,  " + "'<span><strong>会议内容：</strong>' || ap.hynr ||  '</span><br><span><strong>会议室：</strong>'  "
					+ "|| (select wm_concat(hys.hytmc) from OA_T_HWGL_HYSGL hys " + " where hys.id in (select hysap.ncyhys  from OA_T_HWGL_HYAPB_HYSSYSJ hysap where hysap.oa_t_hwgl_hyapb_id = ap.id)) "
					+ " || '</span><br><span><strong>责任部门：</strong>' ||  " + "(select wm_concat(b.NAME) from lborganization b where b.ID in (select * from table(split(ap.zbdw, ';'))))  ||  "
					+ " '</span><br><span><strong>活动召集人：</strong>' || (select u.NAME from tuser u where u.id = ap.sqr) " + " || '</span><br><span><strong>时间：</strong>' ||  (select to_char(gg.kssj, 'hh24:mi:ss') || '-' "
					+ "|| to_char(gg.jssj, 'hh24:mi:ss') from dual)  || '</span><br><span><strong>参加领导：</strong>' " + "|| (select wm_concat(uu.NAME)  from tuser uu where instr(';'||ap.chld||';', ';'||uu.id||';') > 0) "
					+ " || '</span><br><span><strong>参加人员：</strong>' || " + " (select wm_concat(uu.NAME) from tuser uu where instr(';'||ap.yhry||';', ';'||uu.id||';') > 0) " + " || '</span><br><span><strong>与会角色：</strong>' || "
					+ " (select wm_concat(qq.NAME)  from lbrole qq where instr(';'||ap.yhjs||';', ';'||qq.id||';') > 0) " + " || '</span><br><span><strong>与会部门：</strong>' || "
					+ " (select wm_concat(vv.NAME) from lborganization vv where instr(';'||ap.yhbm||';', ';'||vv.id||';') > 0) " + " || '</span><br><span><strong>校外来宾：</strong>' || ap.xwlb || '</span>' as nr  from OA_T_HWGL_HYAPB ap "
					+ " left join t_zc zc  on zc.id = ap.zc left join OA_T_HWGL_HYAPB_HYSSYSJ gg on ap.id=gg.oa_t_hwgl_hyapb_id" + " where to_date(to_char(sysdate, 'yyyyMMdd'), 'yyyyMMdd')  between zc.ksrq and  zc.jsrq group by"
					+ " (gg.kssj,ap.hynr,ap.zbdw, ap.sqr,gg.jssj,ap.chld,ap.yhry,ap.xwlb,ap.id,ap.sqrq,ap.yhjs,ap.yhbm ) order by ap.sqrq, ap.id ";

			List<Map<String, Object>> list = dbutil.queryForList(null, sql);
			StringBuffer sb = new StringBuffer("");
			for (int i = 0; i < 12; i++) {
				sb.append("<tr>");
				for (int j = 1; j <= 7; j++) {
					sb.append("<td>");
					for (Map<String, Object> map : list) {
						if (j == Integer.parseInt(map.get("week").toString())) {
							debug("<<{}>> doView : week : {}", this.getClass().getSimpleName(), map.get("week"));
							sb.append("<div  titleDesc=\"" + map.get("nr") + "\"style=color:white;>※</div>");
							debug("<<{}>> doView : nr : {}", this.getClass().getSimpleName(), map.get("nr"));
							list.remove(map);
							break;
						}
					}
					sb.append("</td>");
				}
				sb.append("</tr>");
			}
			return sb.toString();
		} catch (Exception e) {
			error("<<{}>> getZhy error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		} finally {
			debug("<<{}>> getZhy end : {}", this.getClass().getSimpleName(), timer.end());
		}
	}

	/**
	 * 通知公告明细界面
	 *
	 * @return 通知公告明细界面
	 */
	@RequestMapping(value = ActionConstant.MhShmy.RequestMapping_noticeDetail, method = RequestMethod.GET)
	public String noticeDetail(@PathVariable(ActionConstant.Mh.Variable_db) String db, @PathVariable(ActionConstant.Variable_Id) String id, ModelMap map) {
		debug("<<{}>> noticeDetail start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		try {
			DBMUtil dbmUtil = new DBMUtil(db);
			Tblog blog = dbmUtil.queryBean("select b.*, to_char(b.pubtime, 'yyyy-MM-dd') as fbsj from tblog b where b.id =?", Tblog.class, id);
			//查询所有附件
			List<Lbnewsattachment> attach = dbmUtil.queryAllBeanList("select * from LBNEWSATTACHMENT where tblog_id =?", Lbnewsattachment.class, id);
			blog.setAttach(attach);
			//            String content = blog.getContent().replaceAll("<span[^>]*>", "<span>").replaceAll("<div[^>]*>", "<div>");
			//            blog.setContent(content);
			map.put("blog", blog);
			return ActionConstant.MhShmy.Forward_noticeDetail;
		} catch (Exception e) {
			error("<<{}>> noticeDetail error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		} finally {
			debug("<<{}>> noticeDetail end : {}", this.getClass().getSimpleName(), timer.end());
		}
	}

	/**
	 * 套用frame界面
	 *
	 * @return 套用frame界面
	 */
	@RequestMapping(value = ActionConstant.MhShmy.RequestMapping_frame, method = RequestMethod.GET)
	public String frame(HttpServletRequest request, ModelMap map) {
		debug("<<{}>> frame start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		try {
			map.put("no", request.getParameter("no")); //选中二级菜单的索引
			map.put("url", request.getParameter("q"));
			return ActionConstant.MhShmy.Forward_frame;
		} catch (Exception e) {
			error("<<{}>> frame error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		} finally {
			debug("<<{}>> frame end : {}", this.getClass().getSimpleName(), timer.end());
		}
	}

	public void initchar() {
		lm = new StringBuffer();
		lmbh = new StringBuffer();
		group_num = 0;
		analysis_num = 0;
		id_name_list = new HashMap<String, String>();
		id_value_list = new HashMap<String, String[]>();
		key_value_list = new HashMap<String, String[]>();
	}

	/**
	 * 整理分组条件，把分组名称加入lm中
	 *
	 * @param groupingconditions
	 * @return
	 */
	public void getGroupingconditions(String groupingconditions) {
		String[] idname;
		String[] groupteam = groupingconditions.split(";");
		if (groupteam != null && groupteam.length > 0) {
			group_num = groupteam.length;
			for (int i = 0; i < groupteam.length; i++) {
				idname = groupteam[i].split("=");
				if (idname != null && idname.length == 2) {
					lm.append(idname[0] + ",");
					lmbh.append("value" + i + ",");
					id_name_list.put("value" + i, idname[0]);
					id_value_list.put("value" + i, idname[1].split(","));
				}
			}
		}
	}

	/**
	 * 整理分析项，把分析项名称加入lm中
	 *
	 * @param analysisofitem
	 * @return
	 */
	public void getAnalysisofitem(String analysisofitem) {
		String[] analysisteam = analysisofitem.split(",");
		if (analysisteam != null && analysisteam.length > 0) {
			analysis_num = analysisteam.length;
			for (int i = 0; i < analysisteam.length; i++) {
				lm.append(analysisteam[i] + ",");
				lmbh.append("num" + i + ",");
			}
		}
	}

	/**
	 * 查看图集的详细内容
	 *
	 * @param map
	 * @return
	 */
	@RequestMapping(value = "/uc/shmy/tj-{location}({height})", method = RequestMethod.GET)
	public synchronized String queryEntity(@PathVariable String location, @PathVariable String height, HttpServletRequest request, ModelMap map) {
		debug("<<{}>> tj start", this.getClass().getSimpleName());
		List<String[]> al_new = new ArrayList<String[]>();
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		TSysReportfactory reportfactory = new TSysReportfactory();
		//查询该用户该位置有无添加报表
		String sql_ur = " select wid,rwid,userid,location " + " from t_user_report " + " where 1=1 and location=? and userid=?";
		//查询该用户该位置报表工厂内容
		String sql_rf = " select t.wid,t.chartname,(select code from t_db where wid=t.code) as code,t.querysql,t.charttype,t.analysisofitem,t.groupingconditions " + " from t_sys_reportfactory t " + " where 1=1 and wid=?";
		List<TUserReport> userReportlist = DBUtil.queryAllBeanList(sql_ur, TUserReport.class, location, user.getUserid());
		if (userReportlist != null && userReportlist.size() > 0) {
			TUserReport userReport = userReportlist.get(0);
			if (userReport != null && StringUtil.isNotBlank(userReport.getRwid())) {
				reportfactory = DBUtil.queryBean(sql_rf, TSysReportfactory.class, userReport.getRwid());
				al_new = searchData(reportfactory);
				//如果展示方式是图，整理数据，并生成对应Json
				if (!"list".equals(reportfactory.getCharttype())) {
					al_new = formatDate(reportfactory);
					charDate(request, reportfactory, al_new);
				} else {
					al_new = formatDateforlist(reportfactory);
					String title = reportfactory.getChartname();
					request.setAttribute("title", title);
					request.setAttribute("resultData", al_new);
					request.setAttribute("lm", lm.toString().split(","));
					request.setAttribute("lmbh", lmbh.toString().split(","));
				}
				request.setAttribute("location", location);
				request.setAttribute("height", height);
				//根据图类型调转不同的页面
				return "portal/shmy/tj/" + reportfactory.getCharttype() + "chart";
			} else {
				request.setAttribute("height", height);
				return "portal/shmy/tj/" + location + "_add";
			}
		} else {
			return "portal/shmy/tj/" + location + "_add";
		}
	}

	/**
	 * 图集预览
	 *
	 * @param map
	 * @return
	 */
	@RequestMapping(value = "/uc/shmy/yl-{wid}({height})", method = RequestMethod.GET)
	public String queryYl(@PathVariable String wid, @PathVariable String height, HttpServletRequest request, ModelMap map) {
		debug("<<{}>> tj start", this.getClass().getSimpleName());
		List<String[]> al_new = new ArrayList<String[]>();
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		TSysReportfactory reportfactory = new TSysReportfactory();
		//查询该用户该位置报表工厂内容
		String sql_rf = " select t.wid,t.chartname,(select code from t_db where wid=t.code) as code,t.querysql,t.charttype,t.analysisofitem,t.groupingconditions " + " from t_sys_reportfactory t " + " where 1=1 and wid=?";
		reportfactory = DBUtil.queryBean(sql_rf, TSysReportfactory.class, wid);
		al_new = searchData(reportfactory);
		//如果展示方式是图，整理数据，并生成对应Json
		if (!"list".equals(reportfactory.getCharttype())) {
			al_new = formatDate(reportfactory);
			charDate(request, reportfactory, al_new);
		} else {
			al_new = formatDateforlist(reportfactory);
			String title = reportfactory.getChartname();
			request.setAttribute("title", title);
			request.setAttribute("resultData", al_new);
			request.setAttribute("lm", lm.toString().split(","));
			request.setAttribute("lmbh", lmbh.toString().split(","));
		}
		request.setAttribute("height", height);
		//根据图类型调转不同的页面
		return "portal/shmy/tj/" + reportfactory.getCharttype() + "chart";
	}

	/**
	 * 查询数据，并存入al_new
	 *
	 */
	private List<String[]> searchData(TSysReportfactory reportfactory) {
		List<String[]> al_new = new ArrayList<String[]>();
		initchar();
		getGroupingconditions(reportfactory.getGroupingconditions());
		getAnalysisofitem(reportfactory.getAnalysisofitem());
		TDb t_db = new TDb();
		String codeid = reportfactory.getCode();
		String sql = reportfactory.getQuerysql();
		DBMUtil dbmUtil = new DBMUtil(reportfactory.getCode());
		ResultSet rs = dbmUtil.queryRowSet(reportfactory.getCode(), sql);
		String[] key = lmbh.toString().split(",");
		try {
			while (rs.next()) {
				String[] newline = new String[key.length];
				String resultskey = "";
				for (int i = 0; i < key.length; i++) {
					newline[i] = rs.getString(i + 1);
					if (i < group_num) {
						resultskey += newline[i] + "_";
					}
				}
				if (resultskey.length() > 0) {
					resultskey = resultskey.substring(0, resultskey.length() - 1);
				}
				key_value_list.put(resultskey, newline);
				al_new.add(newline);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return al_new;
	}

	/**
	 * 整理数据集，补充未结果的数据
	 *
	 * @param reportfactory
	 * @return
	 */
	private List<String[]> formatDate(TSysReportfactory reportfactory) {
		int lengthline = group_num + analysis_num;
		List<String[]> al_new = new ArrayList<String[]>();
		if (group_num == 1) {
			String[] idvalue = id_value_list.get("value0");
			for (int n = 0; n < idvalue.length; n++) {
				if (key_value_list.get(idvalue[n]) != null) {
					String[] temp = (String[]) key_value_list.get(idvalue[n]);
					String[] newline = new String[analysis_num];
					for (int m = group_num; m < temp.length; m++) {
						newline[m - group_num] = temp[m] != null ? temp[m].toString() : "";
					}
					al_new.add(newline);
				} else {
					String[] newline = new String[analysis_num];
					for (int m = 0; m < analysis_num; m++) {
						newline[m] = "";
					}
					al_new.add(newline);
				}
			}
		}
		if (group_num == 2) {
			String[] idvalue_1 = id_value_list.get("value0");
			String[] idvalue_2 = id_value_list.get("value1");
			for (int j = 0; j < idvalue_1.length; j++) {
				for (int n = 0; n < idvalue_1.length; n++) {
					if (key_value_list.get(idvalue_1[j] + "_" + idvalue_2[n]) != null) {
						String[] temp = (String[]) key_value_list.get(idvalue_1[j] + "" + idvalue_2[n]);
						String[] newline = new String[analysis_num];
						for (int m = 2; m < temp.length; m++) {
							newline[m] = temp[m] != null ? temp[m].toString() : "";
						}
						al_new.add(newline);
					} else {
						String[] newline = new String[analysis_num];
						for (int m = 2; m < lengthline; m++) {
							newline[m] = "";
						}
						al_new.add(newline);
					}
				}
			}
		}
		if (group_num == 3) {
			String[] idvalue_1 = id_value_list.get("value0");
			String[] idvalue_2 = id_value_list.get("value1");
			String[] idvalue_3 = id_value_list.get("value2");
			for (int i = 0; i < idvalue_1.length; i++) {
				for (int j = 0; j < idvalue_2.length; j++) {
					for (int n = 0; n < idvalue_3.length; n++) {
						if (key_value_list.get(idvalue_1[i] + "_" + idvalue_2[j] + "_" + idvalue_3[n]) != null) {
							String[] temp = (String[]) key_value_list.get(idvalue_1[i] + "_" + idvalue_2[j] + "_" + idvalue_3[n]);
							String[] newline = new String[analysis_num];
							for (int m = 3; m < temp.length; m++) {
								newline[m] = temp[m] != null ? temp[m].toString() : "";
							}
							al_new.add(newline);
						} else {
							String[] newline = new String[analysis_num];
							for (int m = 3; m < lengthline; m++) {
								newline[m] = "";
							}
							al_new.add(newline);
						}
					}
				}
			}
		}
		return al_new;
	}

	/**
	 * 整理数据集，补充未结果的数据
	 *
	 * @param reportfactory
	 * @return
	 */
	private List<String[]> formatDateforlist(TSysReportfactory reportfactory) {
		int lengthline = group_num + analysis_num;
		List<String[]> al_new = new ArrayList<String[]>();
		if (group_num == 1) {
			String[] idvalue = id_value_list.get("value0");
			for (int n = 0; n < idvalue.length; n++) {
				if (key_value_list.get(idvalue[n]) != null) {
					String[] temp = (String[]) key_value_list.get(idvalue[n]);
					al_new.add(temp);
				} else {
					String[] newline = new String[lengthline];
					newline[0] = idvalue[n];
					for (int m = group_num; m < lengthline; m++) {
						newline[m] = "";
					}
					al_new.add(newline);
				}
			}
		}
		if (group_num == 2) {
			String[] idvalue_1 = id_value_list.get("value0");
			String[] idvalue_2 = id_value_list.get("value1");
			for (int j = 0; j < idvalue_1.length; j++) {
				for (int n = 0; n < idvalue_1.length; n++) {
					if (key_value_list.get(idvalue_1[j] + "_" + idvalue_2[n]) != null) {
						String[] temp = (String[]) key_value_list.get(idvalue_1[j] + "" + idvalue_2[n]);
						al_new.add(temp);
					} else {
						String[] newline = new String[lengthline];
						newline[0] = idvalue_1[j];
						newline[1] = idvalue_2[n];
						for (int m = group_num; m < lengthline; m++) {
							newline[m] = "";
						}
						al_new.add(newline);
					}
				}
			}
		}
		if (group_num == 3) {
			String[] idvalue_1 = id_value_list.get("value0");
			String[] idvalue_2 = id_value_list.get("value1");
			String[] idvalue_3 = id_value_list.get("value2");
			for (int i = 0; i < idvalue_1.length; i++) {
				for (int j = 0; j < idvalue_2.length; j++) {
					for (int n = 0; n < idvalue_3.length; n++) {
						if (key_value_list.get(idvalue_1[i] + "_" + idvalue_2[j] + "_" + idvalue_3[n]) != null) {
							String[] temp = (String[]) key_value_list.get(idvalue_1[i] + "_" + idvalue_2[j] + "_" + idvalue_3[n]);
							al_new.add(temp);
						} else {
							String[] newline = new String[lengthline];
							newline[0] = idvalue_1[i];
							newline[1] = idvalue_2[j];
							newline[2] = idvalue_2[n];
							for (int m = group_num; m < lengthline; m++) {
								newline[m] = "";
							}
							al_new.add(newline);
						}
					}
				}
			}
		}
		return al_new;
	}

	/**
	 * 把查询的记录集格式化为输出的样式
	 *
	 * @param reportfactory
	 * @return
	 */
	private void charDate(HttpServletRequest request, TSysReportfactory reportfactory, List<String[]> al_list) {
		String title = reportfactory.getChartname();
		;
		String categories = "";
		String[] temp = lm.toString().split(",");
		String yAxis = "";
		String series = "";
		String pointStart = "";
		String charttype = reportfactory.getCharttype();
		if ("column".equals(charttype)) {
			String[] categories_temp = id_value_list.get("value0");
			for (int i = 0; i < categories_temp.length; i++) {
				categories += categories_temp[i] + ",";
			}
			if (categories.length() > 0) {
				categories = categories.substring(0, categories.length() - 1);
			}
			if (temp != null && temp.length > group_num) {
				yAxis = temp[group_num];
			}
			series = getJsonValue(reportfactory, categories_temp, al_list);
		}
		if ("area".equals(charttype)) {
			String[] categories_temp = id_value_list.get("value0");
			if (categories_temp != null && categories_temp.length > 0) {
				pointStart = categories_temp[0];
			}
			if (temp != null && temp.length > group_num) {
				yAxis = temp[group_num];
			}
			series = getJsonValue(reportfactory, categories_temp, al_list);
		}
		if ("line".equals(charttype)) {
			String[] categories_temp = id_value_list.get("value0");
			for (int i = 0; i < categories_temp.length; i++) {
				categories += categories_temp[i] + ",";
			}
			if (categories.length() > 0) {
				categories = categories.substring(0, categories.length() - 1);
			}
			if (temp != null && temp.length > group_num) {
				yAxis = temp[group_num];
			}
			series = getJsonValue(reportfactory, categories_temp, al_list);
		}
		if ("pie".equals(charttype)) {
			String[] categories_temp = id_value_list.get("value0");
			series = getJsonValueforpic(reportfactory, categories_temp, al_list);
		}
		categories = "'" + categories.replace(",", "','") + "'";
		request.setAttribute("title", title);
		request.setAttribute("categories", categories);
		request.setAttribute("yAxis", yAxis);
		request.setAttribute("series", series);
		request.setAttribute("pointStart", pointStart);

	}

	/**
	 * 根据sql返回json字符串
	 *
	 * @param reportfactory
	 * @return
	 */
	private String getJsonValue(TSysReportfactory reportfactory, String[] categories, List<String[]> al_list) {
		String[] analysisteam = reportfactory.getAnalysisofitem().split(",");
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_temp = new StringBuffer();
		String[] groupteam = categories;
		for (int i = 0; i < analysisteam.length; i++) {
			sb.append("{name:'").append(analysisteam[i]).append("',");
			sb.append("data:[");
			for (int al_list_len = 0; al_list_len < al_list.size(); al_list_len++) {
				String[] temp = al_list.get(al_list_len);
				sb_temp.append(temp[i] != null & temp[i].length() > 0 ? temp[i] : "0").append(",");
			}
			if (sb_temp.length() > 0) {
				sb_temp.deleteCharAt(sb_temp.length() - 1);
			}
			sb.append(sb_temp.toString());
			sb.append("]},");
		}
		if (sb.length() > 0) {
			sb.deleteCharAt(sb.length() - 1);
		}

		return sb.toString();
	}

	/**
	 * 根据sql返回json字符串
	 *
	 * @param reportfactory
	 * @return
	 */
	private String getJsonValueforpic(TSysReportfactory reportfactory, String[] categories, List<String[]> al_list) {
		int fx_num = 0;
		String[] analysisteam = reportfactory.getAnalysisofitem().split(",");
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_temp = new StringBuffer();
		String[] groupteam = categories;
		sb.append("{type: 'pie',name:'").append(analysisteam[0]).append("',");
		sb.append("data:[");
		for (int i = 0; i < analysisteam.length; i++) {
			if (i == fx_num) {
				for (int al_list_len = 0; al_list_len < al_list.size(); al_list_len++) {
					String[] temp = al_list.get(al_list_len);
					sb.append("['" + categories[al_list_len] + "',").append(temp[fx_num] != null & temp[fx_num].length() > 0 ? temp[fx_num] : "0").append("],");
				}
				if (sb.length() > 0) {
					sb.deleteCharAt(sb.length() - 1);
				}
			}
		}
		sb.append("]}");
		return sb.toString();
	}

	/**
	 * 查看图集的详细内容（四级页面）
	 * 
	 * @param map
	 * @return
	 */
	@RequestMapping(value = "/uc/shmy/settj-{location}", method = RequestMethod.GET)
	public String choosereport(@PathVariable String location, HttpServletRequest request, ModelMap map) {
		debug("<<{}>> choosereport start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		String sql_cr = " select t.wid,t.chartname,t.images,t.shuom " + " from T_SYS_REPORTFACTORY t " + " where 1=1 and t.state='1' and not exists (select 1 from t_user_report r where r.userid=? and r.rwid=t.wid )";
		List<TSysReportfactory> reportfactoryList = DBUtil.queryAllBeanList(sql_cr, TSysReportfactory.class, user.getUserid());
		request.setAttribute("reportfactoryList", reportfactoryList);
		request.setAttribute("location", location);
		return "portal/shmy/tj/choosereport";
	}

	/**
	 * 新门户新闻首页展示
	 *
	 * @param map
	 * @return
	 */
	@RequestMapping(value = "/uc/shmy/index/new-{location}({rownum})", method = RequestMethod.GET)
	public String queryIndexNewsEntity(@PathVariable String location, @PathVariable String rownum, HttpServletRequest request, ModelMap map) {
		debug("<<{}>> fdn start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		List<TWz> al_new = Collections.EMPTY_LIST;
		;
		al_new = service.queryNewsEntity(request, location, rownum);
		request.setAttribute("newslist", al_new);
		return "portal/shmy/indexnews";
	}

	/**
	 * 门户前台栏目配置内容展示
	 *
	 * @param map
	 * @return
	 */
	@RequestMapping(value = "/uc/shmy/fdn-{location}({rownum})", method = RequestMethod.GET)
	public String queryNewsEntity(@PathVariable String location, @PathVariable String rownum, HttpServletRequest request, ModelMap map) {
		debug("<<{}>> fdn start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		List<TWz> al_new = Collections.EMPTY_LIST;
		;
		al_new = service.queryNewsEntity(request, location, rownum);
		request.setAttribute("newslist", al_new);
		return "portal/shmy/frontdesknews";
	}

	/**
	 * 文章列表界面
	 *
	 * @return 通知公告明细界面
	 */
	@RequestMapping(value = "/uc/shmy/fdn/{location}", method = RequestMethod.GET)
	public String queryNewsPagesList(@PathVariable String location, HttpServletRequest request, ModelMap map, Pager pager, String gjz) {

		debug("<<{}>> queryNewsPagesList start", this.getClass().getSimpleName());
		LittleTimer timer = new LittleTimer();
		List<TWz> list = Collections.EMPTY_LIST;
		list = service.queryNewsPagesList(request, location, pager, gjz);
		request.setAttribute("resultList", list);
		return "portal/shmy/frontdesknewslist";
	}

	/**
	 * 文章明细界面
	 *
	 * @return 通知公告明细界面
	 */
	@RequestMapping(value = "/uc/shmy/fdnv/{location}-{wid}", method = RequestMethod.GET)
	public String queryNewsBean(@PathVariable String location, @PathVariable String wid, HttpServletRequest request, ModelMap map) {
		LittleTimer timer = new LittleTimer();
		TWz twz = new TWz();
		twz = service.queryNewsBean(request, location, wid);
		request.setAttribute("twz", twz);
		return "portal/shmy/frontdesknewsDetail";
	}

	/**
	 * 流程代码业务导航界面
	 *
	 * @param request --
	 * @return workflowTypes
	 */
	@RequestMapping(value = ActionConstant.MhShmy.RequestMapping_workflow, method = RequestMethod.GET)
	public String workflow(HttpServletRequest request) {
		debug("<<{}>> workflow start", this.getClass().getSimpleName());
		TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
		LittleTimer timer = new LittleTimer();
		try {
			List<TLbxx> workflowTypes = DBUtil.queryAllBeanList("select * from t_lbxx t" + " where t.lx = '1' and t.upid = '1' and t.state='1' order by orderno", TLbxx.class);
			List<TLbxx> workflows = DBUtil.queryAllBeanList("select lb.wid,lb.upid,lb.name," + " db.link||'/StartWorkflow?PageOnly=true&Workflow='||lb.des as url from t_lbxx lb" + " inner join t_sys_role_workflow  rw on lb.wid=rw.workflowid"
					+ " inner join t_sys_user_role ur on ur.roleid= rw.roleid" + " inner join t_db db on db.code=lb.db" + " where lb.lx=1 and lb.upid!='1' and ur.userid=?" + " order by lb.upid,lb.orderno", TLbxx.class, user.getUserid());
			for (int i = 0; i < workflowTypes.size(); i++) {
				TLbxx workflowType = workflowTypes.get(i);
				for (TLbxx workflow : workflows) {
					if (workflow.getUpid().equals(workflowType.getWid())) {
						workflowType.getLbxxList().add(workflow);
					}
				}
				if (workflowType.getLbxxList().isEmpty()) {
					workflowTypes.remove(i);
					i--;
				}
			}
			request.setAttribute("workflowTypes", workflowTypes);
			return ActionConstant.MhShmy.Forward_workflow;
		} catch (Exception e) {
			error("<<{}>> workflow error : {}", this.getClass().getSimpleName(), e);
			throw new RuntimeException(e);
		} finally {
			debug("<<{}>> workflow end : {}", this.getClass().getSimpleName(), timer.end());
		}
	}

}